package com.anolesoft.twelvePlan.mapper;

import com.anolesoft.twelvePlan.entity.MesGyInfoDO;
import com.anolesoft.twelvePlan.entity.MesGyInfoMoreDO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author zyq
 * @date 2022/5/26
 * 工艺计划表序号及子件继承
 */
@Mapper
public interface MesGyInfoMoreMapper extends BaseMapper<MesGyInfoMoreDO> {
    // 生成序号
    @Insert("    insert into mes_gy_info_more\n" +
            "      (mes_gy_info_obj,\n" +
            "       serialnumber,\n" +
            "       parentnum,\n" +
            "       lengthnum,\n" +
            "       jx,\n" +
            "       partid,\n" +
            "       factory_id,\n" +
            "       jsworkshop,\n" +
            "       nianyue)\n" +
            "      select objuid as mes_gy_info_obj,\n" +
            "             serialnumber,\n" +
            "             substr(serialnumber, 1, 9) as parentnum,\n" +
            "             length(serialnumber) as lengthnum,\n" +
            "             jx,\n" +
            "             partid,\n" +
            "             factory_id,\n" +
            "             jsworkshop,\n" +
            "             to_char(add_months(sysdate, {month}),'yyyy-mm') as nianyue\n" +
            "        from (select b.*,\n" +
            "                     substr(sys_connect_by_path(lpad(b.bb, 9, '-'), '.'), 2) serialnumber\n" +
            "                from (select row_number() over(partition by factory_id, jxnum, b.parentpartid order by b.partid) bb,\n" +
            "                             b.*\n" +
            "                        from (select rank() over(order by a.jx) as jxnum, a.*\n" +
            "                                from mes_gy_info a\n" +
            "                               where a.deleteflag = 0) b) b\n" +
            "               start with parentpartid is null\n" +
            "              connect by parentpartid = prior partid\n" +
            "                     and jxnum = prior jxnum\n" +
            "                     and factory_id = prior factory_id\n" +
            "               order by parentpartid, partid) t")
    int xuHao(@Param("month") int month);

    // 匹配信息查询

    List<MesGyInfoMoreDO> selectMatch();

    // 子件继承查询
    @Select("select a.jx,\n" +
            "       a.factory_id,\n" +
            "       a.serialnumber,\n" +
            "       a.grouping_a,\n" +
            "       a.checktime,\n" +
            "       a.isexecutable,\n" +
            "       a.jzjh,\n" +
            "       a.matching,\n" +
            "       a.nianyue\n" +
            "  from mes_gy_info_more a\n" +
            " where a.lengthnum = 9")
    List<MesGyInfoMoreDO> selectParent();

    // 更新子件继承数据
    @Update("update mes_gy_info_more a\n" +
            "   set a.matching     = #{matching},\n" +
            "       a.grouping_a   = #{groupingA},\n" +
            "       a.checktime    = #{checktime},\n" +
            "       a.isexecutable = #{isexecutable},\n" +
            "       a.jzjh         = #{jzjh}\n" +
            " where a.jx = #{jx}\n" +
            "   and a.factory_id = #{factoryId}\n" +
            "   and a.parentnum = #{serialnumber}")
    int updateChild(@Param("mesGyInfoMoreDO") MesGyInfoMoreDO mesGyInfoMoreDO);
}
